using System.Linq;
using System.Text;
using System.Diagnostics;
using System.Runtime.Serialization;
using System.Collections;
using System.Data;
using System.ComponentModel;
using System.Data.OracleClient;
using WPFExcelReport.Helpers;
using WPFExcelReport.Controls;
namespace WPFExcelReport
{
    public partial class VKUNDVER
    {
        public void SetUpNewRow(ref DataRow dwReturn)
        {

            switch (app.company)
            {
                case "YGR":
                case "YCC":
                case "TST":
                default:
                    #region=========
                    dwReturn["KDVMANDANT"] =app.Mandant;
                    dwReturn["KDVKUNDNR"] = "00000000";
                    dwReturn["KDVVB"] = "RMB";
                    dwReturn["KDVVG"] = "   ";
                    dwReturn["KDVSTAKZ"] = "A";
                    dwReturn["KDVSTADAT"] = "20130912";
                    dwReturn["KDVWAEHRSL"] = "RMB";
                    dwReturn["KDVZAHLBED"] = "100";
                    dwReturn["KDVEKON"] = "        ";
                    dwReturn["KDVKDVKL"] = "   ";
                    dwReturn["KDVVALUTA"] = "0";
                    dwReturn["KDVSTEUER"] = "T  ";
                    dwReturn["KDVLIEFBED"] = "EXW";
                    dwReturn["KDVLANDU"] = "   ";
                    dwReturn["KDVPLZU"] = "            ";
                    dwReturn["KDVORTU"] = "                                        ";
                    dwReturn["KDVBZU"] = "  ";
                    dwReturn["KDVBZBEZU"] = "             ";
                    dwReturn["KDVCOUNTRY"] = "    ";
                    dwReturn["KDVCITY"] = "    ";
                    dwReturn["KDVPREISGRP"] = "        ";
                    dwReturn["KDVMINDST"] = "1";
                    dwReturn["KDVSTDBON"] = "        ";
                    dwReturn["KDVLIEFKOMP"] = "N";
                    dwReturn["KDVLIEFAUTO"] = "J";
                    dwReturn["KDVABNGRP"] = "   ";
                    dwReturn["KDVABSSCHL"] = "   ";
                    dwReturn["KDVMUSTER"] = "0";
                    dwReturn["KDVKOLLZYKL"] = "                         ";
                    dwReturn["KDVBONVON"] = "0";
                    dwReturn["KDVBONIN"] = "0";
                    dwReturn["KDVBONBIS"] = "0";
                    dwReturn["KDVABRVB"] = "   ";
                    dwReturn["KDVABRVG"] = "   ";
                    dwReturn["KDVRABABRKZ"] = " ";
                    dwReturn["KDVBONABRKZ"] = " ";
                    dwReturn["KDVGBABRKZ"] = " ";
                    dwReturn["KDVFBABRKZ"] = " ";
                    dwReturn["KDVVBABRKZ"] = " ";
                    dwReturn["KDVGABONA"] = "0";
                    dwReturn["KDVGABONP"] = "0";
                    dwReturn["KDVFIXBONP"] = "0";
                    dwReturn["KDVVORBONP"] = "0";
                    dwReturn["KDVRABART1"] = "   ";
                    dwReturn["KDVRABPROZ1"] = "0";
                    dwReturn["KDVRABART2"] = "   ";
                    dwReturn["KDVRABPROZ2"] = "0";
                    dwReturn["KDVRABART3"] = "   ";
                    dwReturn["KDVRABPROZ3"] = "0";
                    dwReturn["KDVRABART4"] = "   ";
                    dwReturn["KDVRABPROZ4"] = "0";
                    dwReturn["KDVRABART5"] = "   ";
                    dwReturn["KDVRABPROZ5"] = "0";
                    dwReturn["KDVBONWE1"] = "0";
                    dwReturn["KDVBONP1"] = "0";
                    dwReturn["KDVBONWE2"] = "0";
                    dwReturn["KDVBONP2"] = "0";
                    dwReturn["KDVBONWE3"] = "0";
                    dwReturn["KDVBONP3"] = "0";
                    dwReturn["KDVBONWE4"] = "0";
                    dwReturn["KDVBONP4"] = "0";
                    dwReturn["KDVBONWE5"] = "0";
                    dwReturn["KDVBONP5"] = "0";
                    dwReturn["KDVBONWE6"] = "0";
                    dwReturn["KDVBONP6"] = "0";
                    dwReturn["KDVBONWE7"] = "0";
                    dwReturn["KDVBONP7"] = "0";
                    dwReturn["KDVBONWE8"] = "0";
                    dwReturn["KDVBONP8"] = "0";
                    dwReturn["KDVBONWE9"] = "0";
                    dwReturn["KDVBONP9"] = "0";
                    dwReturn["KDVBONWE10"] = "0";
                    dwReturn["KDVBONP10"] = "0";
                    dwReturn["KDVUMSGR"] = "0";
                    dwReturn["KDVDISTRWEG"] = "   ";
                    dwReturn["KDVSTATGEB"] = "   ";
                    dwReturn["KDVSACHB"] = "JAKV    ";
                    dwReturn["KDVVORBONV1"] = "0";
                    dwReturn["KDVVORBONV2"] = "0";
                    dwReturn["KDVVORBONV3"] = "0";
                    dwReturn["KDVBONLDAT"] = "0";
                    dwReturn["KDVVBONLDAT"] = "0";
                    dwReturn["KDVZUREGEL"] = "N";
                    dwReturn["KDVZEITRAUM"] = " ";
                    dwReturn["KDVWOCHTAG"] = "0";
                    dwReturn["KDVKZVORW"] = "N";
                    dwReturn["KDVMONTAG1"] = "00";
                    dwReturn["KDVMONTAG2"] = "00";
                    dwReturn["KDVDATLSR"] = "0";
                    dwReturn["KDVVTRNR1"] = "        ";
                    dwReturn["KDVVTRNR2"] = "        ";
                    dwReturn["KDVVTRNR3"] = "        ";
                    dwReturn["KDVVTRNR4"] = "        ";
                    dwReturn["KDVAZVERB"] = "0";
                    dwReturn["KDVBASIS"] = " ";
                    dwReturn["KDVSPLITT"] = " ";
                    dwReturn["KDVMEEH"] = "   ";
                    dwReturn["KDVAUFMEEH"] = "   ";
                    dwReturn["KDVABRUF"] = "N";
                    dwReturn["KDVZESSIO"] = " ";
                    dwReturn["KDVMAXWERT"] = "0";
                    dwReturn["KDVPREH"] = "   ";
                    dwReturn["KDVFBPROV"] = " ";
                    dwReturn["KDVSPLKEY"] = " ";
                    dwReturn["KDVFILLER"] = "                                                                                                                          ";
                    dwReturn["KDVAENDZL"] = "0";
                    dwReturn["KDVAENDBEN"] = "        ";
                    dwReturn["KDVAENDDAT"] = "0";
                    dwReturn["KDVNEUBEN"] = "JAKV    ";
                    dwReturn["KDVNEUDAT"] = "20130912";
                    #endregion
                    break;
            }
        }

        public void InsertKUNDVER(DataRow dwData, string ITXDBVERSION = "50.24")
        {

            or1 = OracleDB.CreateCommand();
            switch (ITXDBVERSION)
            {
                default:
                case "YGR":
                    or1.CommandText = "INSERT INTO KUNDVER ( KDVMANDANT,KDVKUNDNR,KDVVB,KDVVG,KDVSTAKZ,KDVSTADAT,KDVWAEHRSL,KDVZAHLBED,KDVEKON,KDVKDVKL,KDVVALUTA,KDVSTEUER,KDVLIEFBED,KDVLANDU,KDVPLZU,KDVORTU,KDVBZU,KDVBZBEZU,KDVCOUNTRY,KDVCITY,KDVPREISGRP,KDVMINDST,KDVSTDBON,KDVLIEFKOMP,KDVLIEFAUTO,KDVABNGRP,KDVABSSCHL,KDVMUSTER,KDVKOLLZYKL,KDVBONVON,KDVBONIN,KDVBONBIS,KDVABRVB,KDVABRVG,KDVRABABRKZ,KDVBONABRKZ,KDVGBABRKZ,KDVFBABRKZ,KDVVBABRKZ,KDVGABONA,KDVGABONP,KDVFIXBONP,KDVVORBONP,KDVRABART1,KDVRABPROZ1,KDVRABART2,KDVRABPROZ2,KDVRABART3,KDVRABPROZ3,KDVRABART4,KDVRABPROZ4,KDVRABART5,KDVRABPROZ5,KDVBONWE1,KDVBONP1,KDVBONWE2,KDVBONP2,KDVBONWE3,KDVBONP3,KDVBONWE4,KDVBONP4,KDVBONWE5,KDVBONP5,KDVBONWE6,KDVBONP6,KDVBONWE7,KDVBONP7,KDVBONWE8,KDVBONP8,KDVBONWE9,KDVBONP9,KDVBONWE10,KDVBONP10,KDVUMSGR,KDVDISTRWEG,KDVSTATGEB,KDVSACHB,KDVVORBONV1,KDVVORBONV2,KDVVORBONV3,KDVBONLDAT,KDVVBONLDAT,KDVZUREGEL,KDVZEITRAUM,KDVWOCHTAG,KDVKZVORW,KDVMONTAG1,KDVMONTAG2,KDVDATLSR,KDVVTRNR1,KDVVTRNR2,KDVVTRNR3,KDVVTRNR4,KDVAZVERB,KDVBASIS,KDVSPLITT,KDVMEEH,KDVAUFMEEH,KDVABRUF,KDVZESSIO,KDVMAXWERT,KDVPREH,KDVFBPROV,KDVSPLKEY,KDVFILLER,KDVAENDZL,KDVAENDBEN,KDVAENDDAT,KDVNEUBEN,KDVNEUDAT)"
                    + " VALUES (:KDVMANDANT:,:KDVKUNDNR:,:KDVVB:,:KDVVG:,:KDVSTAKZ:,:KDVSTADAT:,:KDVWAEHRSL:,:KDVZAHLBED:,:KDVEKON:,:KDVKDVKL:,:KDVVALUTA:,:KDVSTEUER:,:KDVLIEFBED:,:KDVLANDU:,:KDVPLZU:,:KDVORTU:,:KDVBZU:,:KDVBZBEZU:,:KDVCOUNTRY:,:KDVCITY:,:KDVPREISGRP:,:KDVMINDST:,:KDVSTDBON:,:KDVLIEFKOMP:,:KDVLIEFAUTO:,:KDVABNGRP:,:KDVABSSCHL:,:KDVMUSTER:,:KDVKOLLZYKL:,:KDVBONVON:,:KDVBONIN:,:KDVBONBIS:,:KDVABRVB:,:KDVABRVG:,:KDVRABABRKZ:,:KDVBONABRKZ:,:KDVGBABRKZ:,:KDVFBABRKZ:,:KDVVBABRKZ:,:KDVGABONA:,:KDVGABONP:,:KDVFIXBONP:,:KDVVORBONP:,:KDVRABART1:,:KDVRABPROZ1:,:KDVRABART2:,:KDVRABPROZ2:,:KDVRABART3:,:KDVRABPROZ3:,:KDVRABART4:,:KDVRABPROZ4:,:KDVRABART5:,:KDVRABPROZ5:,:KDVBONWE1:,:KDVBONP1:,:KDVBONWE2:,:KDVBONP2:,:KDVBONWE3:,:KDVBONP3:,:KDVBONWE4:,:KDVBONP4:,:KDVBONWE5:,:KDVBONP5:,:KDVBONWE6:,:KDVBONP6:,:KDVBONWE7:,:KDVBONP7:,:KDVBONWE8:,:KDVBONP8:,:KDVBONWE9:,:KDVBONP9:,:KDVBONWE10:,:KDVBONP10:,:KDVUMSGR:,:KDVDISTRWEG:,:KDVSTATGEB:,:KDVSACHB:,:KDVVORBONV1:,:KDVVORBONV2:,:KDVVORBONV3:,:KDVBONLDAT:,:KDVVBONLDAT:,:KDVZUREGEL:,:KDVZEITRAUM:,:KDVWOCHTAG:,:KDVKZVORW:,:KDVMONTAG1:,:KDVMONTAG2:,:KDVDATLSR:,:KDVVTRNR1:,:KDVVTRNR2:,:KDVVTRNR3:,:KDVVTRNR4:,:KDVAZVERB:,:KDVBASIS:,:KDVSPLITT:,:KDVMEEH:,:KDVAUFMEEH:,:KDVABRUF:,:KDVZESSIO:,:KDVMAXWERT:,:KDVPREH:,:KDVFBPROV:,:KDVSPLKEY:,:KDVFILLER:,:KDVAENDZL:,:KDVAENDBEN:,:KDVAENDDAT:,:KDVNEUBEN:,:KDVNEUDAT:)";
                    or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
                    break;
                //VAFMANDANT VAFKOLL VAFART VAFFARBE VAFBEZ VAFFFARBE

            }

            Connect2Proxy(or1);
        }

        public void DeleteKUNDVER(DataRow dwData, int option = 1)
        {

            or1 = OracleDB.CreateCommand();
            switch (option)
            {
                case 1: DKDV01(dwData);
                    break;
            }
            Connect2Proxy(or1);
        }
        public void DKDV00(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM KUNDVER"
                    + " WHERE KDVMANDANT=:KDVMANDANT:  ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            Connect2Proxy(or1);
        }
        public void DKDV01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM KUNDVER"
                    + " WHERE KDVMANDANT=:KDVMANDANT: AND KDVKUNDNR=:KDVKUNDNR: AND KDVVB=:KDVVB: AND KDVVG=:KDVVG:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            Connect2Proxy(or1);
        }
        public void DKDV02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM KUNDVER"
                    + " WHERE KDVMANDANT=:KDVMANDANT: AND KDVKUNDNR=:KDVKUNDNR: AND KDVVB=:KDVVB: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            Connect2Proxy(or1);
        }
        public void DKDV03(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "DELETE FROM KUNDVER"
                    + " WHERE KDVMANDANT=:KDVMANDANT: AND KDVKUNDNR=:KDVKUNDNR: ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);

            Connect2Proxy(or1);
        }
        public void UpdateKUNDVER(DataRow dwData, int option)
        {
            if (option == 0)
            {
                switch (app.company)
                {
                    default:
                        option = 1;
                        break;
                }
            }
            or1 = OracleDB.CreateCommand();

            switch (option)
            {
                case 1: UKDV01(dwData); break;
            }


        }
        public void UKDV01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "UPDATE KUNDVER SET KDVMANDANT=:KDVMANDANT:,KDVKUNDNR=:KDVKUNDNR:,KDVVB=:KDVVB:,KDVVG=:KDVVG:,KDVSTAKZ=:KDVSTAKZ:,KDVSTADAT=:KDVSTADAT:,KDVWAEHRSL=:KDVWAEHRSL:,KDVZAHLBED=:KDVZAHLBED:,KDVEKON=:KDVEKON:,KDVKDVKL=:KDVKDVKL:,KDVVALUTA=:KDVVALUTA:,KDVSTEUER=:KDVSTEUER:,KDVLIEFBED=:KDVLIEFBED:,KDVLANDU=:KDVLANDU:,KDVPLZU=:KDVPLZU:,KDVORTU=:KDVORTU:,KDVBZU=:KDVBZU:,KDVBZBEZU=:KDVBZBEZU:,KDVCOUNTRY=:KDVCOUNTRY:,KDVCITY=:KDVCITY:,KDVPREISGRP=:KDVPREISGRP:,KDVMINDST=:KDVMINDST:,KDVSTDBON=:KDVSTDBON:,KDVLIEFKOMP=:KDVLIEFKOMP:,KDVLIEFAUTO=:KDVLIEFAUTO:,KDVABNGRP=:KDVABNGRP:,KDVABSSCHL=:KDVABSSCHL:,KDVMUSTER=:KDVMUSTER:,KDVKOLLZYKL=:KDVKOLLZYKL:,KDVBONVON=:KDVBONVON:,KDVBONIN=:KDVBONIN:,KDVBONBIS=:KDVBONBIS:,KDVABRVB=:KDVABRVB:,KDVABRVG=:KDVABRVG:,KDVRABABRKZ=:KDVRABABRKZ:,KDVBONABRKZ=:KDVBONABRKZ:,KDVGBABRKZ=:KDVGBABRKZ:,KDVFBABRKZ=:KDVFBABRKZ:,KDVVBABRKZ=:KDVVBABRKZ:,KDVGABONA=:KDVGABONA:,KDVGABONP=:KDVGABONP:,KDVFIXBONP=:KDVFIXBONP:,KDVVORBONP=:KDVVORBONP:,KDVRABART1=:KDVRABART1:,KDVRABPROZ1=:KDVRABPROZ1:,KDVRABART2=:KDVRABART2:,KDVRABPROZ2=:KDVRABPROZ2:,KDVRABART3=:KDVRABART3:,KDVRABPROZ3=:KDVRABPROZ3:,KDVRABART4=:KDVRABART4:,KDVRABPROZ4=:KDVRABPROZ4:,KDVRABART5=:KDVRABART5:,KDVRABPROZ5=:KDVRABPROZ5:,KDVBONWE1=:KDVBONWE1:,KDVBONP1=:KDVBONP1:,KDVBONWE2=:KDVBONWE2:,KDVBONP2=:KDVBONP2:,KDVBONWE3=:KDVBONWE3:,KDVBONP3=:KDVBONP3:,KDVBONWE4=:KDVBONWE4:,KDVBONP4=:KDVBONP4:,KDVBONWE5=:KDVBONWE5:,KDVBONP5=:KDVBONP5:,KDVBONWE6=:KDVBONWE6:,KDVBONP6=:KDVBONP6:,KDVBONWE7=:KDVBONWE7:,KDVBONP7=:KDVBONP7:,KDVBONWE8=:KDVBONWE8:,KDVBONP8=:KDVBONP8:,KDVBONWE9=:KDVBONWE9:,KDVBONP9=:KDVBONP9:,KDVBONWE10=:KDVBONWE10:,KDVBONP10=:KDVBONP10:,KDVUMSGR=:KDVUMSGR:,KDVDISTRWEG=:KDVDISTRWEG:,KDVSTATGEB=:KDVSTATGEB:,KDVSACHB=:KDVSACHB:,KDVVORBONV1=:KDVVORBONV1:,KDVVORBONV2=:KDVVORBONV2:,KDVVORBONV3=:KDVVORBONV3:,KDVBONLDAT=:KDVBONLDAT:,KDVVBONLDAT=:KDVVBONLDAT:,KDVZUREGEL=:KDVZUREGEL:,KDVZEITRAUM=:KDVZEITRAUM:,KDVWOCHTAG=:KDVWOCHTAG:,KDVKZVORW=:KDVKZVORW:,KDVMONTAG1=:KDVMONTAG1:,KDVMONTAG2=:KDVMONTAG2:,KDVDATLSR=:KDVDATLSR:,KDVVTRNR1=:KDVVTRNR1:,KDVVTRNR2=:KDVVTRNR2:,KDVVTRNR3=:KDVVTRNR3:,KDVVTRNR4=:KDVVTRNR4:,KDVAZVERB=:KDVAZVERB:,KDVBASIS=:KDVBASIS:,KDVSPLITT=:KDVSPLITT:,KDVMEEH=:KDVMEEH:,KDVAUFMEEH=:KDVAUFMEEH:,KDVABRUF=:KDVABRUF:,KDVZESSIO=:KDVZESSIO:,KDVMAXWERT=:KDVMAXWERT:,KDVPREH=:KDVPREH:,KDVFBPROV=:KDVFBPROV:,KDVSPLKEY=:KDVSPLKEY:,KDVFILLER=:KDVFILLER:,KDVAENDZL=:KDVAENDZL:,KDVAENDBEN=:KDVAENDBEN:,KDVAENDDAT=:KDVAENDDAT:,KDVNEUBEN=:KDVNEUBEN:,KDVNEUDAT=:KDVNEUDAT:"
                 + " WHERE KDVMANDANT=:KDVMANDANT: AND KDVKUNDNR=:KDVKUNDNR: AND KDVVB=:KDVVB: AND KDVVG=:KDVVG:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            Connect2Proxy(or1);
        }

        public DataSet QueryKUNDVER(DataRow dwData, int option = 1)
        {    
            switch (option)
            {
                
                case 1:
                default:
                    SResult = QKDV01(dwData);
                    break;
                case 2: SResult = QKDV02(dwData); break;
            } 

             return SResult;
        }
        public DataSet QKDV01(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM KUNDVER    "
                   + " WHERE KDVMANDANT=:KDVMANDANT: AND KDVKUNDNR=:KDVKUNDNR: AND KDVVB=:KDVVB: AND KDVVG=:KDVVG:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;  
            return SResult;
        }
        public DataSet QKDV02(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM KUNDVER  "
                  + " WHERE KDVMANDANT=:KDVMANDANT: AND KDVKUNDNR=:KDVKUNDNR:  ";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData); 
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QKDV03(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT * FROM KUNDVER    "
                   + " WHERE KDVMANDANT=:KDVMANDANT: AND KDVKUNDNR=:KDVKUNDNR: AND KDVVB=:KDVVB:";
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QKDV04(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT KDVVG AS KEY, K.* FROM KUNDVER K  "
                  + " WHERE KDVMANDANT=:KDVMANDANT:   " + dwData["KDVKUNDNR"].ToString()
                  + dwData["KDVVB"].ToString()
                  + dwData["KDVVG"].ToString();
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
        public DataSet QKDV05(DataRow dwData)
        {
            or1 = OracleDB.CreateCommand();
            or1.CommandText = "SELECT DISTINCT KDVVB AS KEY FROM KUNDVER K  "
                  + " WHERE KDVMANDANT=:KDVMANDANT:   " + dwData["KDVKUNDNR"].ToString() 
                  + dwData["KDVVG"].ToString();
            or1.CommandText = INTEXDBC.InjectData(or1.CommandText, dwData);
            SResult = Connect2Proxy(or1);
            SResult.Tables[0].TableName = TableName;
            return SResult;
        }
    }
}
